/*
 * FindNeighboursCommand.java
 *
 * Created on October 4, 2007, 8:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.io.BufferedReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;

/**
 *
 * @author Asger
 */
public class FindNeighboursCommand extends PromptingCommand {
    
    private FindSeatsCommand findSeatsCommand;
    
    public Ticket ticket;
    
    /** Creates a new instance of FindNeighboursCommand */
    public FindNeighboursCommand(BufferedReader input, FindSeatsCommand findSeats) {
        super(input);
        findSeatsCommand = findSeats;
    }
    
    public void execute() throws SQLException {        
        ArrayList<Reservation> reservations = ticket.fetchReservations(connection);
        
        if (reservations.size() == 0) {
            System.out.println("-------------------------------");
            System.out.println("This ticket has no booked seats.");
            System.out.println("-------------------------------");
            return;
        }
        
        System.out.println("-------------------------------");
        System.out.println("You have " + reservations.size() + " seats reserved for this ticket:");
        for (int i=0; i<reservations.size(); i++) {
            System.out.println("  " + (i+1) + ". " + reservations.get(i).seat + " for " + reservations.get(i).route);
        }
        System.out.println("-------------------------------");
        
        int seatNum = 0;
        if (reservations.size() > 1) {
            System.out.println("Please enter the seat you wish to find neighbours for:");
            seatNum = promptInt(1, reservations.size()) - 1;
        }
        
        TrainRoute route = reservations.get(seatNum).route;
        Seat seat = reservations.get(seatNum).seat;
        
        HashSet<Seat> seats = new HashSet<Seat>();
        ArrayList<Seat> neighbours = getFreeNeighboursOnConnection(route.connections.get(0), ticket.firstClass, seat);
        seats.addAll( neighbours );
        for (int i=1; i<route.connections.size(); i++)
        {
            neighbours = getFreeNeighboursOnConnection(route.connections.get(i), ticket.firstClass, seat);
            seats.retainAll(neighbours);
        }
        
        // All neighbours found now. Let's print them out.
        System.out.println("-------------------------------");
        System.out.println("We found " + seats.size() + " free seats near " + seat.row + seat.position + " in wagon " + seat.wagonNumber);
        for (Seat s : seats) {
            System.out.println(s);
        }
    }
    
    public ArrayList<Seat> getFreeNeighboursOnConnection(TrainConnection con, boolean firstClass, Seat seat) throws SQLException
    {
        char fcChar = firstClass? 'A':'B';
        String sql = "SELECT * FROM seats s NATURAL JOIN wagons w NATURAL JOIN transports t NATURAL JOIN connections c WHERE " +
                "c.departure="+con.departure+" AND c.train_number="+con.trainNumber+" AND w.wagon_number="+seat.wagonNumber + " AND " +
                "s.class='"+fcChar+"' AND c.to_name='"+escapeSQL(con.to)+"' AND c.from_name='"+escapeSQL(con.from)+"' AND " +
                "NOT EXISTS (SELECT * FROM reservations r WHERE r.wagon_number=s.wagon_number AND r.seat_row=s.seat_row AND " +
                        "r.seat_position=s.seat_position AND r.departure="+con.departure+") AND " +
                "EXISTS (SELECT * FROM neighbours n WHERE n.wagon_number="+seat.wagonNumber+" AND " +
                    "((n.row1="+seat.row+" AND n.position1='"+seat.position+"' AND n.row2=s.seat_row AND n.position2=s.seat_position) OR " +
                      "(n.row1=s.seat_row AND n.position1=s.seat_position AND n.row2="+seat.row+" AND n.position2='"+seat.position+"')))";
        ResultSet result = connection.createStatement().executeQuery(sql);
        
        ArrayList<Seat> seats = new ArrayList<Seat>();
        while (result.next()) {
            Seat s = Seat.parse(result, "s.");
            seats.add(s);
        }
        return seats;
    }
}



